using System;
using System.Data;
using System.Data.SqlClient;
using Widgetsphere.Core.Logging;

namespace Widgetsphere.Core.DataAccess
{
	/// <summary>
	/// Summary description for DbHelper.
	/// </summary>
	public partial class DatabaseHelper
	{
		private static readonly TraceSwitch _logSwitch = LogClass.WSCommonDataAccess;
		private DatabaseHelper()
		{
		}

		#region SQL
		[Obsolete("This should not be used in new code", false)]
		public static DataSet ExecuteDataset(string connectionString, string storedProcedureName, SqlParameter[] arParms)
		{
			var retVal = new DataSet();

			var connection = GetConnection(connectionString);
			var command = GetCommand();
			command.CommandText = storedProcedureName;
			command.CommandType = CommandType.StoredProcedure;
			foreach (var param in arParms)
			{
				command.Parameters.Add(param);
			}
			command.Connection = connection;
			var da = new SqlDataAdapter();
			da.SelectCommand = (SqlCommand)command;

			try
			{
				da.Fill(retVal);
				connection.Open();
			}
			catch (Exception ex)
			{
				MessageLog.LogError(_logSwitch, ex);
			}
			finally
			{
				if (connection.State != ConnectionState.Closed)
				{
					connection.Close();
				}
			}
			return retVal;
		}

		[Obsolete("This should not be used in new code", false)]
		public static void ExecuteNonQuerySql(string connectionString, string commandText, SqlParameter[] arParms)
		{
			var connection = GetConnection(connectionString);
			var command = GetCommand();
			command.CommandText = commandText;
			command.CommandType = CommandType.Text;
			foreach (var param in arParms)
			{
				command.Parameters.Add(param);
			}
			command.Connection = connection;

			try
			{
				connection.Open();
				command.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				MessageLog.LogError(_logSwitch, ex);
			}
			finally
			{
				if (connection.State != ConnectionState.Closed)
				{
					connection.Close();
				}
			}
		}

		[Obsolete("This should not be used in new code", false)]
		public static DataSet ExecuteSql(string connectionString, string commandText, SqlParameter[] arParms)
		{
			var retVal = new DataSet();

			var connection = GetConnection(connectionString);
			var command = GetCommand();
			command.CommandText = commandText;
			command.CommandType = CommandType.Text;
			foreach (var param in arParms)
			{
				command.Parameters.Add(param);
			}
			command.Connection = connection;
			var da = new SqlDataAdapter();
			da.SelectCommand = (SqlCommand)command;

			try
			{
				da.Fill(retVal);
			}
			catch (Exception ex)
			{
				MessageLog.LogError(_logSwitch, ex);
			}
			finally
			{
				if (connection.State != ConnectionState.Closed)
				{
					connection.Close();
				}
			}
			return retVal;
		}

		[Obsolete("This should not be used in new code", false)]
		public static void ExecuteNonQuery(string connectionString, string storedProcedureName, SqlParameter[] arParms)
		{

			var connection = GetConnection(connectionString);
			var command = GetCommand();
			command.CommandText = storedProcedureName;
			command.CommandType = CommandType.StoredProcedure;
			foreach (var param in arParms)
			{
				command.Parameters.Add(param);
			}
			command.Connection = connection;
			try
			{
				connection.Open();
				command.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				MessageLog.LogError(_logSwitch, ex);
			}
			finally
			{
				if (connection.State != ConnectionState.Closed)
				{
					connection.Close();
				}
			}

		}

		public static int ExecuteNonQuery(IDbCommand command)
		{
			var cmd = (SqlCommand)command;
			return cmd.ExecuteNonQuery();
		}

		public static IDbConnection GetConnection(string connectionString)
		{
			var conn = new SqlConnection();
			conn.ConnectionString = connectionString;
			return conn;
		}

		public static IDbCommand GetCommand(string connectionString, string spName)
		{

			var sp = StoredProcedureFactory.GetStoredProcedure(connectionString, spName);
			return sp.GetDirectQueryCommand();
		}

		public static void SetParameterValue(IDbCommand command, string paramName, object inValue)
		{
			try
			{
				var currentParam = (IDbDataParameter)command.Parameters[paramName];
				if (currentParam != null)
				{
					currentParam.Value = inValue;
				}
				else
				{
					throw new Widgetsphere.Core.Exceptions.WSException("Parameter: " + paramName + " is missing from stored procedure: " + command.CommandText);
				}
			}
			catch (Exception ex)
			{
				throw new Widgetsphere.Core.Exceptions.WSException("Parameter: " + paramName + " is missing from stored procedure: " + command.CommandText, ex);
			}
		}

		internal static IDataReader ExecuteReader(IDbCommand selectCommand)
		{
			var cmd = (SqlCommand)selectCommand;
			IDataReader dr = cmd.ExecuteReader();
			return dr;
		}

		internal static IDbDataAdapter GetDataAdapter()
		{
			var da = new SqlDataAdapter();
			return da;
		}

		internal static IDbCommand GetCommand()
		{
			var cmd = new SqlCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			return cmd;
		}

		internal static void Fill(IDbDataAdapter da, DataTable dt)
		{
			var sqlDa = (SqlDataAdapter)da;
			sqlDa.Fill(dt);
		}

		internal static void Update(IDbDataAdapter da, DataTable dt)
		{
			var sqlDa = (SqlDataAdapter)da;
			if (dt != null && dt.Rows.Count > 0)
			{
				try
				{
					sqlDa.Update(dt);
				}
				catch (Exception ex)
				{
					Console.Write(ex.ToString());
					throw ex;
				}
			}
		}
		internal static void Update(IDbDataAdapter da, DataRow[] rows, DataRowState drs)
		{
			var command = GetCommand(da, drs);
			try
			{
				MessageLog.LogInfo(_logSwitch, "Begin Update {0}", command != null ? command.CommandText : "command name passed as 'null'");
				Update(da, rows);
			}
			catch (Exception ex)
			{
				MessageLog.LogWarning(_logSwitch, ex);
				LogParams(command);
				throw;
			}
			finally
			{
				MessageLog.LogInfo(_logSwitch, "End Update {0}", command != null ? command.CommandText : "command name passed as 'null'");
			}
		}

		private static void Update(IDbDataAdapter da, DataRow[] rows)
		{
			var sqlDa = (SqlDataAdapter)da;
			if (rows.Length > 0)
			{
				try
				{
					sqlDa.Update(rows);
				}
				catch (Exception ex)
				{
					Console.Write(ex.ToString());
					throw ex;
				}
			}
		}
		#endregion

		#region Logging Helper Methods
		private static IDbCommand GetCommand(IDbDataAdapter da, DataRowState drs)
		{
			IDbCommand retVal = null;
			if (drs == DataRowState.Added)
			{
				retVal = da.InsertCommand;
			}
			else if (drs == DataRowState.Deleted)
			{
				retVal = da.DeleteCommand;
			}
			else if (drs == DataRowState.Modified)
			{
				retVal = da.UpdateCommand;
			}
			return retVal;
		}

		private static void LogParams(IDbCommand command)
		{

			if (command != null)
			{
				MessageLog.LogInfo(_logSwitch, "Stored Procedure '{0}' failed", command.CommandText);
				foreach (IDataParameter dp in command.Parameters)
				{
					MessageLog.LogInfo(_logSwitch, "Parameter Name: {0}  Parameter Value: {1}  Source Column: {2}", dp.ParameterName, dp.Value, dp.SourceColumn);
				}
			}
			else
			{
				MessageLog.LogInfo(_logSwitch, "IDbCommand was null");

			}
		}
		#endregion

		internal static void SetTransaction(IDbDataAdapter da, IDbTransaction trans)
		{
			if (da.InsertCommand != null)
			{
				da.InsertCommand.Transaction = trans;
			}
			if (da.SelectCommand != null)
			{
				da.SelectCommand.Transaction = trans;
			}
			if (da.UpdateCommand != null)
			{
				da.UpdateCommand.Transaction = trans;
			}
			if (da.DeleteCommand != null)
			{
				da.DeleteCommand.Transaction = trans;
			}
		}

		internal static void SetConnections(IDbDataAdapter da, IDbConnection conn)
		{
			if (da.InsertCommand != null)
			{
				da.InsertCommand.Connection = conn;
			}
			if (da.SelectCommand != null)
			{
				da.SelectCommand.Connection = conn;
			}
			if (da.UpdateCommand != null)
			{
				da.UpdateCommand.Connection = conn;
			}
			if (da.DeleteCommand != null)
			{
				da.DeleteCommand.Connection = conn;
			}
		}

		public static bool CheckDbConnection(string connectionString)
		{
			SqlConnection con = null;
			bool result;
			var added = false;
			try
			{
				var conntokens = connectionString.Split(new char[] { ';' });
				string connstr = null;
				foreach (var conntoken in conntokens)
				{
					if (conntoken.Trim().ToLower().StartsWith("timeout"))
					{
						if (connstr == null)
							connstr = "Timeout=5";
						else
							connstr += ";Timeout=5";
						added = true;
					}
					else if (connstr == null)
						connstr = conntoken;
					else
						connstr += ";" + conntoken;
				}
				if (!added)
					connstr += ";Timeout=5";

				con = new SqlConnection(connstr);

				con.Open();
				result = (con.State == ConnectionState.Open);
			}
			catch
			{
				result = false;
			}
			finally
			{
				if ((con != null) && (con.State != ConnectionState.Closed))
					con.Close();
			}
			return result;
		}
	}
}
